using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class PopulateOnDemandPage : System.Web.UI.Page
{
    Label Label1=null;
    GridView gridView=null;
    TreeView TreeView1=null;
    
    protected void Page_Load(object sender, EventArgs e)
    {
          TreeView1 = new TreeView();          
          TreeView1.PopulateNodesFromClient=true;
          TreeView1.ExpandDepth=0;
          TreeNode treeNode = new TreeNode();
          treeNode.Text="Sale Orders";
          treeNode.PopulateOnDemand=true;
          treeNode.SelectAction= TreeNodeSelectAction.Expand;
          TreeView1.EnableClientScript = false;
          TreeView1.Nodes.Add(treeNode);
          TreeView1.TreeNodePopulate += new TreeNodeEventHandler(TreeView1_TreeNodePopulate);
          TreeView1.SelectedNodeChanged += new EventHandler(TreeView1_SelectedNodeChanged);
          this.PanelForTreeView.Controls.Add(TreeView1);
          gridView = new GridView();
          this.PanelForData.Controls.Add(gridView);
          Label1 = new Label();
          this.form1.Controls.Add(Label1);
    }

    void TreeView1_SelectedNodeChanged(object sender, EventArgs e)
    {
        PopulateProducts(gridView ,TreeView1.SelectedValue); 
    }

    void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
    {        
        switch (e.Node.Depth)
        {
            case 0:
                PopulateCategories(e.Node);
                break;
            case 1:
                PopulateProducts(e.Node);
                break;
        }
    }

    void PopulateCategories(TreeNode node)
    {

        DataTable dataTable = GetDataTable(
        "Select CategoryID, CategoryName From Categories");
        if (dataTable.Rows.Count > 0)
        {
            foreach (DataRow row in dataTable.Rows)
            {
                TreeNode newNode = new TreeNode();
                newNode.Text = row["CategoryName"].ToString();
                newNode.Value = row["CategoryID"].ToString();   
                newNode.PopulateOnDemand = true;
                newNode.SelectAction = TreeNodeSelectAction.Expand;
                node.ChildNodes.Add(newNode);
            }
        }
    }

    void PopulateProducts(TreeNode node)
    {
        DataTable dataTable = GetDataTable(
        "Select ProductName,ProductID " +
        " From Products Where CategoryID=" 
        + node.Value);
        if (dataTable.Rows.Count > 0)
        {
            foreach (DataRow row in dataTable.Rows)
            {
                TreeNode NewNode = new TreeNode(
                row["ProductName"].ToString(), 
                row["ProductID"].ToString());
                NewNode.PopulateOnDemand = false;
                              
                NewNode.SelectAction = TreeNodeSelectAction.Select;
                node.ChildNodes.Add(NewNode);
            }
        }
    }
    void PopulateProducts(GridView gridView, string ProductID)
    {
        DataTable dataTable = GetDataTable(
        "Select  * " +
        " From [Order Details] Where ProductID="
        + ProductID);
        if (dataTable.Rows.Count > 0)
        {
             gridView.DataSource =dataTable;
             gridView.DataBind();              
        }
    }
    DataTable GetDataTable(String queryString)
    {
        String ConnectionString = "server=(local);"+
        "database=NorthWind;Integrated Security=SSPI";
        //"database=1NorthWind;Integrated Security=SSPI";

        SqlConnection sqlConnection = new SqlConnection(ConnectionString);
        SqlDataAdapter sqlDataAdapter;
        DataTable dataTable = new DataTable();

        try
        {
            sqlDataAdapter = new SqlDataAdapter(queryString, sqlConnection);
            sqlDataAdapter.Fill(dataTable);
            sqlConnection.Close();
        }
        catch (Exception ex)
        {
            if (sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
            Label1.Text = "Unable to connect to the database: " 
            + ex.Message ;
        }
        finally
        {
            sqlConnection.Dispose();
        }

        return dataTable;

    }

}
